package org.dna.mybatis.criteria;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import java.util.logging.Logger;
import org.apache.ibatis.exceptions.PersistenceException;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.ScriptRunner;

/**
 * Static inner class to verify and create DB schema
 * */
public class DBInit {

    public static interface IDBRunnable {

        public void run(Connection c) throws SQLException;
    }

    private static final String DB_SCHEMA_PATH = "org/dna/mybatis/criteria/ddl/hsql/schema.sql";
    public static String DRIVER;
    public static String USERNAME;
    public static String PASSWORD;
    public static String URL;
    protected static boolean s_schemaInitialized;
    private static final Logger LOG = Logger.getLogger("org.dna.mybatis.criteria");

    private static void initScriptRunner(Connection conn) throws IOException, SQLException {
        ScriptRunner runner = new ScriptRunner(conn);
        StringWriter errStringWriter = new StringWriter();
        PrintWriter errorPrintw = new PrintWriter(errStringWriter);
        StringWriter logStringWriter = new StringWriter();
        PrintWriter logPrintw = new PrintWriter(logStringWriter);

        runner.setErrorLogWriter(errorPrintw);
        runner.setLogWriter(logPrintw);

        runner.runScript(Resources.getResourceAsReader(DB_SCHEMA_PATH));
        LOG.severe(errStringWriter.toString());
        LOG.info(logStringWriter.toString());
    }

    private synchronized static void createSchema(Connection conn) throws PersistenceException {
        if (s_schemaInitialized) {
            LOG.info("schema already created");
            return;
        }

        try {
            initScriptRunner(conn);
        } catch (IOException e) {
            LOG.severe("Error in creating DB schema, file error" + e);
            throw new PersistenceException(e);
        } catch (SQLException e) {
            LOG.severe("Error in creating DB schema, sql error" + e);
            throw new PersistenceException(e);
        }

        // create new admin User
        // LOG("inserting default admin user into database");
        LOG.info("schema creation complete");
        s_schemaInitialized = true;
    }

    public static void createSchema() {
        try {
            Properties props = Resources.getResourceAsProperties("org/dna/mybatis/criteria/db.properties");
            URL = props.getProperty("url") + ":" + props.getProperty("db.type")
                    + ":" + props.getProperty("db.name");
            DRIVER = props.getProperty("driver");
            USERNAME = props.getProperty("username");
            PASSWORD = props.getProperty("password");
            Class.forName(DRIVER).newInstance();
            Connection conn = DriverManager.getConnection(URL, USERNAME,
                    PASSWORD);

            try {
                createSchema(conn);
            } finally {
                conn.close();
            }
        } catch (Exception e) {
            throw new RuntimeException("Description.  Cause: " + e, e);
        }
    }

    public static void rundbTransactionExecutor(IDBRunnable task) throws SQLException {
        //Create a DB connection
        Connection c = DriverManager.getConnection(URL, USERNAME,
                    PASSWORD);

        try {
            task.run(c);
        } finally {
            c.commit();
            c.close();
        }
    }

    public static void updateQuery(final String query) throws SQLException {
        rundbTransactionExecutor(new IDBRunnable() {

            public void run(Connection c) throws SQLException {
                Statement s = c.createStatement();
                try {
                    s.executeUpdate(query);
                } catch (SQLException se) {
                    se.printStackTrace();
                } finally {
                    s.close();
                }
            }
        });
    }

    /**
     * Execute the select operation
     */
    public static void executeSql(String query) throws SQLException {
        sqlOperation(query);
    }

    /**
     * Execute the select query and return a list of maps,
     */
    //TODO refactor code equals to printQueryResults changes only one line
    private static List<Map<String, Object>> sqlOperation(final String sqlOperation) throws SQLException {
        final List<Map<String, Object>> res = new ArrayList<Map<String, Object>>();

        rundbTransactionExecutor(new IDBRunnable() {

            public void run(Connection c) throws SQLException {
                Statement s = c.createStatement();
                ResultSet rs = null;
                try {
                    if (s.execute(sqlOperation)) {
                        rs = s.getResultSet();
                        res.addAll(resultSet2ListTable(rs));
                    }
                } /*catch (SQLException se) {
                se.printStackTrace(System.err);
                }*/ finally {
                    if (rs != null) {
                        rs.close();
                    }
                    s.close();
                }
            }
        });
        return res;
    }


    public static List<Map<String, Object>> resultSet2ListTable(ResultSet rs) throws SQLException {
        ResultSetMetaData m = rs.getMetaData();
        int cols = m.getColumnCount();
        List<Map<String, Object>> resTable = new ArrayList<Map<String, Object>>();

        while (rs.next()) {
            Map<String, Object> row = new HashMap<String, Object>(cols);
            for (int i = 1; i <= cols; i++) {
                String colName = m.getColumnLabel(i);
                row.put(colName, rs.getObject(i));
            }
            resTable.add(row);
        }
        return resTable;
    }

}
